package com.pjb.springbootjjwt.store;

import com.pjb.springbootjjwt.store.entity.Error;
import com.pjb.springbootjjwt.store.entity.*;
import com.pjb.springbootjjwt.store.utils.FileUtil;
import com.pjb.springbootjjwt.store.utils.SqlUtil;
import com.pjb.springbootjjwt.store.utils.StringUtil;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Sql {

    public static Sql getInstance() {
        return new Sql();
    }

    public static void main(String[] args) throws Exception {
        String sql = "insert into vv('1a', '2b', '3c')";
        List<String> value = getInstance().getValue(sql);
        String c = FileUtil.jsonStr(value);
        System.out.println("value:" + FileUtil.toJson(c, List.class));

        getInstance().getClause("select * from vv where a = '0001' and b = '2bv'");
        getInstance().getSets("update vv set b = '2bc' where a = '0001' and b = '2b'");

        Map map = getInstance().getField("create table test(a vchar, primary key(c), b vchar, c vchar)");
        System.out.println("map:" + map);

    }

    /**
     * 获取pk字段
     *
     * @param ddl
     * @return
     */
    public String getPK(String ddl) {
        String[] fields = Defines.splitDDL(ddl);
        for (String f : fields) {
            String[] pair = f.trim().split("\\s+");
            String typeStr = pair[1];
            if (typeStr.contains("key")) {
                return typeStr
                        .replaceAll("key\\(", "")
                        .replaceAll("\\)", "").trim();
            }
        }
        return "";
    }

    /**
     * 获取pk字段所在的序号
     *
     * @param ddl
     * @return
     */
    public int getPKIndex(String ddl) {
        String pk = getPK(ddl);
        return getFieldIndex(ddl, pk);
    }

    /**
     * 解析ddl, 获取字段和字段类型
     *
     * @param ddl
     * @return
     */
    public Map getField(String ddl) {
        Map<String, String> field = new HashMap<>();
        String[] fields = Defines.splitDDL(ddl);
        for (String f : fields) {
            String[] pair = f.trim().split("\\s+");
            String column = pair[0];
            String typeStr = pair[1];
            if (!typeStr.contains("key"))
                field.put(column, typeStr);
        }
        return field;
    }

    /**
     * 获取字段所在的序号
     *
     * @param ddl
     * @param f
     * @return
     */
    private int getFieldIndex(String ddl, String f) {
        String[] fields = Defines.splitDDL(ddl);
        int count = 0;
        if (fields.length > 0)
            for (String field : fields) {
                if (field.contains("key")) continue;
                if (field.trim().split("\\s+")[0].contains(f)) {
                    return count;
                }
                count++;
            }
        return -1;
    }

    /**
     * 获取字段所在的序号
     *
     * @param table
     * @param f
     * @return
     * @throws IOException
     */
    public int getFieldIndexByTable(Table table, String f) throws IOException {
        if (f.contains(table.getAlias())) f = f.replaceAll(table.getAlias() + ".", "").trim();

        String sql = File.getInstance().getDDL(table.getTable());
        return getFieldIndex(sql, f);
    }

    /**
     * 在多表的情况下返回字段的索引
     *
     * @param f
     * @param tables
     * @return
     * @throws IOException
     */
    public int getFieldIndexByMultiTables(String f, List<Table> tables) throws IOException {
        for (Table table : tables) {
            if (f.contains(table.getAlias())) {
                int init = SqlUtil.getInitIndex(table.getAlias(), tables);
                return getFieldIndexByTable(table, f) + init;
            }
        }
        return -1;
    }

    /**
     * 获取表中所有的字段
     *
     * @return
     */
    public List<String> getFields(Table table) throws IOException {
        String ddl = File.getInstance().getDDL(table.getTable());
        return getFields(ddl);
    }

    /**
     * 获取表中所有字段
     *
     * @param ddl
     * @return
     */
    public List<String> getFields(String ddl) {
        String[] fields = Defines.splitDDL(ddl);
        List<String> r = new ArrayList<>();
        if (fields.length > 0)
            for (String field : fields) {
                if (field.contains("key")) continue;
                r.add(field.split("\\(")[0]);
            }
        return r;
    }

    /**
     * 获取行长度
     *
     * @param ddl
     * @return
     */
    public int getLineLength(String ddl) {
        List<Integer> list = Defines.getFieldsLengths(ddl);
        int len = 0;
        for (Integer i : list) {
            len += i;
        }
        return len;
    }

    /**
     * 获取新增字段
     *
     * @param insertSql
     * @return
     */
    public List<String> getValue(String insertSql) {
        String subSql = insertSql.substring(insertSql.indexOf("(") + 1, insertSql.indexOf(")"));
        List<String> field = new ArrayList<>();
        String[] fields = subSql.split(",");
        for (String f : fields) {
            field.add(f.replaceAll("'", "").trim());
        }
        return field;
    }

    /**
     * 获取条件字段
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Clause> getClause(String sql) throws Exception {
        String sub = StringUtil.sub(sql, "where");
        if (StringUtils.isEmpty(sub)) return null;

        return Clause.clause(sub);
    }

    /**
     * 获取更新字段
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public List<Clause> getSets(String sql) throws Exception {
//        if (!sql.contains("set")) return null;
//
//        if (sql.contains("where")) {
//            sql = sql.substring(sql.indexOf("set"), sql.indexOf("where"));
//        }
//        String subSql = sql.substring(sql.indexOf("set")).replaceAll("set", "");
        String subSql = StringUtil.sub(sql, "set", "where");
        if (StringUtils.isEmpty(subSql)) return null;

        if (subSql.contains("!") || subSql.contains(">") || subSql.contains("<"))
            throw new Exception("" + Response.error.set(Error.E90005, subSql));

        return Clause.clause(subSql);
    }

    /**
     * 获取查询字段
     *
     * @param sql
     * @return
     * @throws IOException
     */
    public List<Integer> getSelectField(String sql) throws IOException {
        List<Table> tables = getFrom(sql);
        if (sql.contains("join")) {
            tables = getJoinFrom(sql);
        }
        if (!ObjectUtils.isEmpty(tables)) {
            if (tables.size() == 1)
                return getSelectField(tables.get(0), sql);
            else if (tables.size() > 1)
                return getSelectField(tables, sql);
        }
        return new ArrayList<>();
    }

    public List<Integer> getSelectField(String sql, Head head) throws IOException {
        String[] array = parseField(sql);
        List<Integer> list = new ArrayList<>();
        if (ObjectUtils.isEmpty(array)) return list;

        if (array.length > 0) {
            List<String> alias = head.getData();
            for (String field : array) {
                field = field.trim();
                if (alias.contains(field)) list.add(alias.indexOf(field));
            }
        }
        return list;
    }

    /**
     * 获取查询字段需要的序号
     *
     * @param table
     * @param sql
     * @return
     * @throws IOException
     */
    public List<Integer> getSelectField(Table table, String sql) throws IOException {
        String[] array = parseField(sql);
        List<Integer> list = new ArrayList<>();
        if (ObjectUtils.isEmpty(array)) return list;

        if (array.length > 0) {
            for (String key : array) {
                int index = getFieldIndexByTable(table, key.trim());
                list.add(index);
            }
        }
        System.out.println("list:" + list);
        return list;
    }

    /**
     * 在多个表联合查询的情况下获取，查询字段需要的序号
     *
     * @param tables
     * @param sql
     * @return
     * @throws IOException
     */
    public List<Integer> getSelectField(List<Table> tables, String sql) throws IOException {
        String[] array = parseField(sql);
        List<Integer> list = new ArrayList<>();
        if (ObjectUtils.isEmpty(array)) return list;

        if (array.length > 0) {
            list = SqlUtil.getIndex(array, tables);
        }
        return list;
    }

    /**
     * 根据sql组织查询字段
     *
     * @param sql
     * @return
     */
    private String[] parseField(String sql) {
        String subSql = StringUtil.sub(sql, "select", "from");
        if (subSql.equals("*"))
            return new String[0];

        String[] arr = subSql.trim().split(",");
        String[] r = new String[arr.length];
        for (int i = 0; i < arr.length; i++) {
            String field = arr[i];
            String[] kv = field.trim().split("\\s+");
            r[i] = kv[0];
        }

        return r;
    }

    /**
     * 获取表名字
     *
     * @param sql
     * @return
     */
    public List<Table> getFrom(String sql) {
        String subSql = StringUtil.sub(sql, "from", "where");
        if (StringUtils.isEmpty(subSql)) return new ArrayList<>();

        String[] array = subSql.split(",");
        return getTables(array);
    }

    private List<Table> getTables(String[] array) {
        List<Table> list = new ArrayList<>();
        if (array.length > 0) {
            for (String key : array) {
                list.add(Table.toTable(key));
            }
        }
        return list;
    }

    public List<Table> getJoinFrom(String sql) {
        String subSql = StringUtil.sub(sql, "from", "where");
        String[] arr = getTableArr(subSql);

        return getTables(arr);
    }

    public String[] getTableArr(String subSql) {
        String[] arr = subSql.split("(left|right|inner|outer)\\s+join");
        for (int i = 0; i < arr.length; i++) {
            String s = arr[i];
            if (s.contains("on")) {
                arr[i] = s.split("on")[0].trim();
            } else {
                arr[i] = s.trim();
            }
        }
        return arr;
    }

    public List<Clause> getJoinClause(String sql) throws Exception {
        sql = StringUtil.sub(sql, "from", "where");
        String[] arr = sql.split("left|right|inner|outer|join|on|and");
        List<String> list = new ArrayList<>();
        for (String s : arr) {
            if (Clause.is(s)) {
                list.add(s);
            }
        }
        return Clause.getClauseList(list.toArray(new String[0]));
    }

}
